﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;

namespace fetco.data
{
    public class Connector
    {
        private string connectstring = @"Database=WEBFETCO;User ID=webdev;Password=webdev;Trusted_Connection=False;";
        private SqlConnection sqlconnect;
        private SqlCommand sqlcommand;
        private SqlDataAdapter sqladapter;
        private static Connector _instance;

        public static Connector GetInstance()
        {
            if (_instance == null)
            {
                _instance = new Connector();
                _instance.Connect();
            }
            return _instance;
        }

        public Connector()
        {
            sqlconnect = new SqlConnection();
            sqlcommand = new SqlCommand();
            sqladapter = new SqlDataAdapter();
        }

        ~Connector()
        {
            this.CloseConnect();
        }

        private bool Connect()        // connect data base 
        {
            try
            {
                string hostserver = @"Server=";
                hostserver += ConfigurationManager.ConnectionStrings["RemoteHost"].ConnectionString;
                connectstring = hostserver + ";" + connectstring;                
                sqlconnect.ConnectionString = connectstring;
                sqlconnect.Open();
                sqlcommand.Connection = sqlconnect;
                return true;
            }
            catch
            {
                return false;
            }
        }

        private void CloseConnect()   // close connect database
        {
            try
            {

                if (sqlconnect.State == ConnectionState.Open)
                    sqlconnect.Close();
                sqlconnect.Dispose();
            }
            catch
            {
            }
        }

        public int ExcuteNonQuery(string question)
        {
            try
            {
                sqlcommand.CommandText = question;
                sqlcommand.CommandType = CommandType.Text;
                return sqlcommand.ExecuteNonQuery();
            }
            catch
            {
                return 0;
            }
        }

        public DataSet ExcuteDataSet(string question)
        {
            try
            {
                sqlcommand.CommandText = question;
                sqladapter.SelectCommand = sqlcommand;
                DataSet data = new DataSet();
                sqladapter.Fill(data, "MyT");
                return data;
            }
            catch
            {
                return null;
            }
        }

        public SqlDataReader ExcuteReader(string query)
        {
            try
            {
                sqlcommand.CommandText = query;
                sqlcommand.CommandType = CommandType.Text;
                return sqlcommand.ExecuteReader();
            }
            catch
            {
                return null;
            }
        }

        public long GetMaxID(string tablename)
        {
            try
            {
                sqlcommand.CommandText = @"select max(id) as NewId from " + tablename;
                sqlcommand.CommandType = CommandType.Text;
                using (SqlDataReader reader = sqlcommand.ExecuteReader())
                {
                    if (reader.Read())
                    {
                        if (!reader.IsDBNull(reader.GetOrdinal("NewId")))
                            return (long)reader["NewId"];
                    }
                }
            }
            catch
            {
                return 0;
            }
            return 0;
        }

        public object GetMaxValue(string fieldname,string tablename)
        {
            try
            {
                sqlcommand.CommandText = @"select max("+fieldname+") as NewId from " + tablename;
                sqlcommand.CommandType = CommandType.Text;
                using (SqlDataReader reader = sqlcommand.ExecuteReader())
                {
                    if (reader.Read())
                    {
                        if (!reader.IsDBNull(reader.GetOrdinal("NewId")))
                            return reader["NewId"];
                    }
                }
            }
            catch
            {
                return 0;
            }
            return 0;
        }

        public long GetTotalRecord(string tablename, string condition)
        {
            long value = 0;
            try
            {
                string query = @"select count(*) as TotalRecord from " + tablename;
                query += " where ";
                query += condition;
                sqlcommand.CommandText = query;
                sqlcommand.CommandType = CommandType.Text;
                using (SqlDataReader reader = sqlcommand.ExecuteReader())
                {
                    if (reader.Read())
                    {
                        if (!reader.IsDBNull(reader.GetOrdinal("TotalRecord")))
                        {
                            value = Convert.ToInt64(reader["TotalRecord"]);
                        }
                    }
                }
            }
            catch
            {
                return value;
            }
            return value;
        }

        public string PagingQuery(string tablename, string fieldname, string condition, string orderbyfield, OrderByDirection orderby, int currpage, int pagesize, long totalrecord)
        {
            long currtotal = (long)(currpage * pagesize);
            long numrow = totalrecord - currtotal;
            string query = "";
            string query1 = "", query2 = "";
            if (numrow >= 0)
            {
                query1 = @"select Top " + currtotal.ToString();
                query1 += " " + fieldname;
                query1 += " from " + tablename + " as T1";
                query1 += " where " + condition;
                query1 += " order by " + orderbyfield + " " + orderby.ToString();

                query2 = @"select Top " + pagesize.ToString() + " *";
                query2 += " from (" + query1 + ") as T2 order by " + orderbyfield + " ";
                if (orderby == OrderByDirection.ASC)
                    query2 += OrderByDirection.DESC.ToString();
                else
                    query2 += OrderByDirection.ASC.ToString();
            }
            else
            {
                numrow = totalrecord % pagesize;
                query2 = @"select Top " + numrow.ToString();
                query2 += " " + fieldname;
                query2 += " from " + tablename + " as T1";
                query2 += " where " + condition;
                query2 += " order by " + orderbyfield + " ";
                if (orderby == OrderByDirection.ASC)
                    query2 += OrderByDirection.DESC.ToString();
                else
                    query2 += OrderByDirection.ASC.ToString();
            }
            query = @"select * from (" + query2 + ") as T3 order by " + orderbyfield + " " + orderby.ToString();
            return query;
        }
    }

    public enum OrderByDirection
    {
        ASC,
        DESC
    }
}
